package myclass;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mysql.cj.util.StringUtils;

public class huodong {
	public void shanchu(String huzhu) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/teacher_system";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		String sql="delete from huodong where zhuti = ?";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1, huzhu);
		int count=ptmt.executeUpdate();
		ptmt.close();
		conn.close();
	}
	public int xiugai(String zhuti,String mudi,String leixing,String shijian,String didian,String shetuan,String quanti,String leirong,String anpai) throws Exception 
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/teacher_system";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		
		String sql="update huodong "
				+ "set mudi=?,"
				+ "leixing=?,"
				+ "shijian=?,"
				+ "didian=?,"
				+ "shetuan=?,"
				+ "quanti=?,"
				+ "leirong=?,"
				+ "anpai=?"
				+ "where zhuti= ? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
			ptmt.setString(1, mudi);
			ptmt.setString(2, leixing);
			ptmt.setString(3, shijian);
			ptmt.setString(4, didian);
			ptmt.setString(5, shetuan);
			ptmt.setString(6, quanti);
			ptmt.setString(7, leirong);
			ptmt.setString(8, anpai);
			ptmt.setString(9, zhuti);
		int count=ptmt.executeUpdate();
		ptmt.close();
		conn.close();
		if(count>0)
		{
			return 1;
		}
		else {
			return 0;
		}
	}
	
	
	public String browse() throws Exception 
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/teacher_system";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		String sql="select * from huodong";
		String str = null;
		//String sql="select * from people where huzhu = ? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ResultSet rs =ptmt.executeQuery();
		int i=0;
		while(rs.next())
		{
			int id=rs.getInt(1);
			String cname=rs.getString(2);
			String tname=rs.getString(3);
			String cwhere=rs.getString(5);
			String zuihou=rs.getString(7);
			String dsadw=rs.getString(8);
			if(i==0)
			{
				i=1;
				if(zuihou!=null&&dsadw!=null)
					str=cname+"  "+tname+"  "+cwhere+"  "+zuihou+"  "+dsadw+".";
					else if(zuihou==null&&dsadw!=null)
					{
						str=cname+"  "+tname+"  "+cwhere+"  "+dsadw+".";
					}
					else if(zuihou!=null&&dsadw==null)
					{
						str=cname+"  "+tname+"  "+cwhere+"  "+zuihou+".";
					}
			}
			else
			{
				if(zuihou!=null&&dsadw!=null)
					str=str+cname+"  "+tname+"  "+cwhere+"  "+zuihou+"  "+dsadw+".";
					else if(zuihou==null&&dsadw!=null)
					{
						str=str+cname+"  "+tname+"  "+cwhere+"  "+dsadw+".";
					}
					else if(zuihou!=null&&dsadw==null)
					{
						str=str+cname+"  "+tname+"  "+cwhere+"  "+zuihou+".";
					}
			}
		}
		ptmt.close();
		conn.close();
		return str;
	}
	public String chaxun(String huzhu,String sex,String mingzu,String jiaoyu) throws Exception 
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/teacher_system";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		String hh=huzhu;
		String sql="select * from huodong where zhuti = ? ";
		String str = null;
		//String sql="select * from people where huzhu = ? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1,hh);
		ResultSet rs =ptmt.executeQuery();
		while(rs.next())
		{
			int id=rs.getInt(1);
			String cname=rs.getString(2);
			String tname=rs.getString(3);
			String cwhere=rs.getString(5);
			String zuihou=rs.getString(7);
			String dsadw=rs.getString(8);
			if(zuihou!=null&&dsadw!=null)
			str=cname+" "+tname+" "+cwhere+" "+zuihou+" "+dsadw+".";
			else if(zuihou==null&&dsadw!=null)
			{
				str=cname+" "+tname+" "+cwhere+" "+dsadw+".";
			}
			else if(zuihou!=null&&dsadw==null)
			{
				str=cname+" "+tname+" "+cwhere+" "+zuihou+".";
			}
		}
		ptmt.close();
		conn.close();
		return str;
	}
	public int add(String zhuti,String mudi,String leixing,String shijian,String didian,String shetuan,String quanti,String leirong,String anpai) throws SQLException, ClassNotFoundException
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/teacher_system";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		
		String sql="insert into huodong(zhuti,mudi,leixing,shijian,didian,shetuan,quanti,leirong,anpai) values(?,?,?,?,?,?,?,?,?)";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1, zhuti);
		ptmt.setString(2, mudi);
		ptmt.setString(3, leixing);
		ptmt.setString(4, shijian);
		ptmt.setString(5, didian);
		ptmt.setString(6, shetuan);
		ptmt.setString(7, quanti);
		ptmt.setString(8, leirong);
		ptmt.setString(9, anpai);
		int count=ptmt.executeUpdate();
		ptmt.close();
		conn.close();
		if(count>0)
		{
			return 1;
		}
		else {
			return 0;
		}
	}
	public int[] mohuchaxun(String zhuti,String shijian,String didian,String leixing) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/teacher_system";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		int arr[]=new int[15];
		String sql="select * from huodong where zhuti like '%"+zhuti+"%' and shijian like '%"+shijian+"%' and didian like '%"+didian+"%'";
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		int i=0;                             
		while(rs.next())
		{
			arr[i]=rs.getInt(1);
			i++;
		}
		rs.close();
		stmt.close();
		conn.close();
		return arr;
	}
	public HuoDongLei show(int id) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/teacher_system";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		String sql="select * from huodong where id = ? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setInt(1,id);
		ResultSet rs = ptmt.executeQuery();
		HuoDongLei hd=new HuoDongLei();
		while(rs.next())
		{
			hd.id=rs.getInt(1);
			hd.zhuti=rs.getString(2);
			hd.shijian=rs.getString(5);
			hd.leixing=rs.getString(4);
			hd.shetuan=rs.getString(7);
			hd.quanti=rs.getString(8);
			hd.didian=rs.getString(6);
		}
		rs.close();
		ptmt.close();
		conn.close();
		return hd;
	}
public String chaxun1(String huzhu,String sex,String mingzu,String jiaoyu) throws Exception 
{
	Class.forName("com.mysql.jdbc.Driver");//注册
	String url="jdbc:mysql://localhost:3306/teacher_system";
	String username="root";
	String Password="qq2665410029";
	Connection conn = DriverManager.getConnection(url, username, Password);//连接
	String hh=sex;
	String sql="select * from huodong where shijian = ? ";
	String str = null;
	//String sql="select * from people where huzhu = ? ";
	PreparedStatement ptmt=conn.prepareStatement(sql);
	ptmt.setString(1,hh);
	ResultSet rs =ptmt.executeQuery();
	while(rs.next())
	{
		int id=rs.getInt(1);
		String cname=rs.getString(2);
		String tname=rs.getString(3);
		String cwhere=rs.getString(5);
		String zuihou=rs.getString(7);
		String dsadw=rs.getString(8);
		if(zuihou!=null&&dsadw!=null)
		str=cname+" "+tname+" "+cwhere+" "+zuihou+" "+dsadw+".";
		else if(zuihou==null&&dsadw!=null)
		{
			str=cname+" "+tname+" "+cwhere+" "+dsadw+".";
		}
		else if(zuihou!=null&&dsadw==null)
		{
			str=cname+" "+tname+" "+cwhere+" "+zuihou+".";
		}
	}
	ptmt.close();
	conn.close();
	return str;
}
public String chaxun2(String huzhu,String sex,String mingzu,String jiaoyu) throws Exception 
{
	Class.forName("com.mysql.jdbc.Driver");//注册
	String url="jdbc:mysql://localhost:3306/teacher_system";
	String username="root";
	String Password="qq2665410029";
	Connection conn = DriverManager.getConnection(url, username, Password);//连接
	String hh=mingzu;
	String sql="select * from huodong where leixing = ? ";
	String str = null;
	//String sql="select * from people where huzhu = ? ";
	PreparedStatement ptmt=conn.prepareStatement(sql);
	ptmt.setString(1,hh);
	ResultSet rs =ptmt.executeQuery();
	while(rs.next())
	{
		int id=rs.getInt(1);
		String cname=rs.getString(2);
		String tname=rs.getString(3);
		String cwhere=rs.getString(5);
		String zuihou=rs.getString(7);
		String dsadw=rs.getString(8);
		if(zuihou!=null&&dsadw!=null)
		str=cname+" "+tname+" "+cwhere+" "+zuihou+" "+dsadw+".";
		else if(zuihou==null&&dsadw!=null)
		{
			str=cname+" "+tname+" "+cwhere+" "+dsadw+".";
		}
		else if(zuihou!=null&&dsadw==null)
		{
			str=cname+" "+tname+" "+cwhere+" "+zuihou+".";
		}
	}
	ptmt.close();
	conn.close();
	return str;
}
public String chaxun3(String huzhu,String sex,String mingzu,String jiaoyu) throws Exception 
{
	Class.forName("com.mysql.jdbc.Driver");//注册
	String url="jdbc:mysql://localhost:3306/teacher_system";
	String username="root";
	String Password="qq2665410029";
	Connection conn = DriverManager.getConnection(url, username, Password);//连接
	String hh=jiaoyu;
	String sql="select * from huodong where shetuan or quanti = ? ";
	String str = null;
	//String sql="select * from people where huzhu = ? ";
	PreparedStatement ptmt=conn.prepareStatement(sql);
	ptmt.setString(1,hh);
	ResultSet rs =ptmt.executeQuery();
	while(rs.next())
	{
		int id=rs.getInt(1);
		String cname=rs.getString(2);
		String tname=rs.getString(3);
		String cwhere=rs.getString(5);
		String zuihou=rs.getString(7);
		String dsadw=rs.getString(8);
		if(zuihou!=null&&dsadw!=null)
		str=cname+" "+tname+" "+cwhere+" "+zuihou+" "+dsadw+".";
		else if(zuihou==null&&dsadw!=null)
		{
			str=cname+" "+tname+" "+cwhere+" "+dsadw+".";
		}
		else if(zuihou!=null&&dsadw==null)
		{
			str=cname+" "+tname+" "+cwhere+" "+zuihou+".";
		}
	}
	ptmt.close();
	conn.close();
	return str;
}
}
